<?php
ini_set('display_errors','on');
error_reporting(E_ALL);
if (php_sapi_name()!=='cli') die('only cli alowed');
if (!isset($argv[1])) die("Use php ".__FILE__." xxx.log\n");
$file = $argv[1];
if (!file_exists($file)) die("File not exists!\n");
if (!preg_match('/tracking_(\d{10})\.log$/', $file, $m)) {
    die("Log file name should like tracking_2012051920.log");
}
//db link
$pdo = new PDO(
    'mysql:host=localhost;dbname=tracking',
    'root',
    '',
    array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
);
$date = substr($m[1],0,8);
if ($pdo->query("SELECT * FROM dumplog WHERE id={$m[1]}")->rowCount() > 0) {
    die("This log file was already dumped. Exit.\n");
}
$pdo->exec("INSERT INTO dumplog VALUES({$m[1]})");
//create daily tables if needed
if ($pdo->query("SHOW TABLES LIKE 'pv_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `pv_$date` (
      `pvid` varchar(32) default NULL,
      `uid` varchar(32) default NULL,
      `sid` varchar(32) default NULL,
      `url` varchar(255) default NULL,
      `ua` varchar(255) default NULL,
      `ip` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `pvid` (`pvid`),
      KEY `uid` (`uid`),
      KEY `sid` (`sid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
}
if ($pdo->query("SHOW TABLES LIKE 'img_clk_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `img_clk_$date` (
      `adurl` varchar(255) default NULL,
      `ourl` varchar(255) default NULL,
      `pvid` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `pvid` (`pvid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ");
}
if ($pdo->query("SHOW TABLES LIKE 'img_imp_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `img_imp_$date` (
      `adurl` varchar(255) default NULL,
      `pvid` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `pvid` (`pvid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
}
if ($pdo->query("SHOW TABLES LIKE 'kw_imp_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `kw_imp_$date` (
      `kw` varchar(255) default NULL,
      `pos1` tinyint(2) default NULL,
      `pos2` tinyint(2) default NULL,
      `adid` varchar(32) default NULL,
      `adurl` varchar(255) default NULL,
      `pvid` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `kw` (`kw`),
      KEY `adid` (`adid`),
      KEY `pvid` (`pvid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
}
if ($pdo->query("SHOW TABLES LIKE 'kw_clk_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `kw_clk_$date` (
      `kw` varchar(255) default NULL,
      `pos1` tinyint(2) default NULL,
      `pos2` tinyint(2) default NULL,
      `ourl` varchar(255) default NULL,
      `adid` varchar(32) default NULL,
      `adurl` varchar(255) default NULL,
      `pvid` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `kw` (`kw`),
      KEY `adid` (`adid`),
      KEY `pvid` (`pvid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
}
if ($pdo->query("SHOW TABLES LIKE 'qry_$date'")->rowCount() == 0) {
    $pdo->query("CREATE TABLE `qry_$date` (
      `type` enum('kw_match','img_match') default NULL,
      `matches` text,
      `pvid` varchar(32) default NULL,
      `ts` int(10) default NULL,
      KEY `type` (`type`),
      KEY `pvid` (`pvid`),
      KEY `ts` (`ts`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8");
}
//prepare query
$sts['pv']      = $pdo->prepare("INSERT INTO `pv_$date`      VALUES (:pvid, :uid, :sid, :url, :ua, :ip, :ts)");
$sts['qry']     = $pdo->prepare("INSERT INTO `qry_$date`     VALUES (:type, :matches, :pvid, :ts)");
$sts['img_clk'] = $pdo->prepare("INSERT INTO `img_clk_$date` VALUES (:adurl, :ourl, :pvid, :ts)");
$sts['img_imp'] = $pdo->prepare("INSERT INTO `img_imp_$date` VALUES (:adurl, :pvid, :ts)");
$sts['kw_imp']  = $pdo->prepare("INSERT INTO `kw_imp_$date`  VALUES (:kw, :pos1, :pos2, :adid, :adurl, :pvid, :ts)");
$sts['kw_clk']  = $pdo->prepare("INSERT INTO `kw_clk_$date`  VALUES (:kw, :pos1, :pos2, :ourl, :adid, :adurl, :pvid, :ts)");
//open logs
$handle = @fopen($file, "r");
$result = array("success"=>0, "fail"=>0);
if ($handle) {
    while (($buffer = fgets($handle, 4096)) !== false) {
        if (strlen(trim($buffer))<2) continue;
        $rtn = dump_data(json_decode(stripslashes($buffer), true), $sts);
        if ($rtn) {
            $result['success'] ++;
        } else {
            $result['fail'] ++;
        }
    }
    if (!feof($handle)) {
        echo "Error: unexpected fgets() fail\n";
    }
    fclose($handle);
}
var_dump($m[1],$result);
echo "\nDone.\n";

function dump_data ($d, $sts) {
    if (!$d) return false;
    if (isset($d['user_id'])) {
        $sts['pv']->execute(array('pvid'=>$d['pv_id'], 'uid'=>$d['user_id'], 'sid'=>$d['session_id'], 'url'=>$d['page_url'], 'ua'=>$d['user_agent'], 'ip'=>$d['ip'], 'ts'=>$d['time_stamp']));
    } else if ($d['type']=='kw_match' || $d['type']=='img_match') {
        $sts['qry']->execute(array('type'=>$d['type'], 'matches'=>$d['matches'], 'pvid'=>$d['pv_id'], 'ts'=>$d['time_stamp']));
    } else if ($d['type']=='impression') {
        if (isset($d['kw'])) {
            $sts['kw_imp']->execute(array('kw'=>$d['kw'], 'pos1'=>$d['pos1'], 'pos2'=>$d['pos2'], 'adid'=>$d['adid'], 'adurl'=>$d['adurl'], 'pvid'=>$d['pv_id'], 'ts'=>$d['time_stamp']));
        } else {
            $sts['img_imp']->execute(array('adurl'=>$d['adurl'], 'pvid'=>$d['pv_id'], 'ts'=>$d['time_stamp']));
        }
    } else if ($d['type']=='click') {
        if (isset($d['kw'])) {
            $sts['kw_clk']->execute(array('kw'=>$d['kw'], 'pos1'=>$d['pos1'], 'pos2'=>$d['pos2'], 'adid'=>$d['adid'], 'adurl'=>$d['adurl'], 'pvid'=>$d['pv_id'], 'ts'=>$d['time_stamp'], 'ourl'=>@$d['ourl']));
        } else {
            $sts['img_clk']->execute(array('adurl'=>$d['adurl'], 'pvid'=>$d['pv_id'], 'ts'=>$d['time_stamp'], 'ourl'=>$d['ourl']));
        }
    } else {
        var_dump($d);//wrong data
    }
    return true;
}
